﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Interfaces;
using System.Data.SQLite;
using BusinessObjects;
using DCMS_Framework;
using System.Data;

namespace DataAccess {
  /// <summary>
  /// Data Access Layer to ZipCodes table
  /// </summary>
  public class ZipcodeDal : IZipcodeLogic {

    #region - Public methods -

    /// <summary>
    /// Gets the zipcode.
    /// </summary>
    /// <param name="id">The id.</param>
    /// <returns></returns>
    public CityZipcode GetZipcode( int id ) {
      CityZipcode zipcode = new CityZipcode( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectZipcodeCommand( connection, id ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                zipcode.ZipcodeId = reader.GetInt32( reader.GetOrdinal( "ZipcodeId" ) );
                zipcode.Zipcode = reader.GetString( reader.GetOrdinal( "Zipcode" ) );
                zipcode.City = reader.GetString( reader.GetOrdinal( "City" ) );
                zipcode.CountryName = reader.GetString( reader.GetOrdinal( "Country" ) );
                zipcode.CountryCode = reader.GetString( reader.GetOrdinal( "CountryCode" ) );
                zipcode.CountryId = reader.GetInt32( reader.GetOrdinal( "CountryId" ) );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex ); ;
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return zipcode;
    }

    /// <summary>
    /// Gets all zipcodes.
    /// </summary>
    /// <returns></returns>
    public SortableBindingList<CityZipcode> GetAllZipcodes( ) {
      SortableBindingList<CityZipcode> list = new SortableBindingList<CityZipcode>( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectAllCommand( connection ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                CityZipcode zipcode = new CityZipcode( );
                zipcode.ZipcodeId = reader.GetInt32( reader.GetOrdinal( "ZipcodeId" ) );
                zipcode.Zipcode = reader.GetString( reader.GetOrdinal( "Zipcode" ) );
                zipcode.City = reader.GetString( reader.GetOrdinal( "City" ) );
                zipcode.CountryName = reader.GetString( reader.GetOrdinal( "Country" ) );
                zipcode.CountryCode = reader.GetString( reader.GetOrdinal( "CountryCode" ) );
                zipcode.CountryId = reader.GetInt32( reader.GetOrdinal( "CountryId" ) );
                list.Add( zipcode );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return list;
    }

    /// <summary>
    /// Gets the zipcodes by country.
    /// </summary>
    /// <param name="countryId">The country id.</param>
    /// <returns></returns>
    public SortableBindingList<CityZipcode> GetZipcodesByCountry( int countryId ) {
      SortableBindingList<CityZipcode> list = new SortableBindingList<CityZipcode>( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectByCountryCommand( connection, countryId ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                CityZipcode zipcode = new CityZipcode( );
                zipcode.ZipcodeId = reader.GetInt32( reader.GetOrdinal( "ZipcodeId" ) );
                zipcode.Zipcode = reader.GetString( reader.GetOrdinal( "Zipcode" ) );
                zipcode.City = reader.GetString( reader.GetOrdinal( "City" ) );
                zipcode.CountryName = reader.GetString( reader.GetOrdinal( "Country" ) );
                zipcode.CountryCode = reader.GetString( reader.GetOrdinal( "CountryCode" ) );
                zipcode.CountryId = reader.GetInt32( reader.GetOrdinal( "CountryId" ) );
                list.Add( zipcode );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return list;
    }

    /// <summary>
    /// Saves the zipcode.
    /// </summary>
    /// <param name="zipcode">The zipcode.</param>
    public void SaveZipcode( CityZipcode zipcode ) {
      if ( zipcode.ZipcodeId == 0 ) {
        InsertZipcode( zipcode );
      } else {
        UpdateZipcode( zipcode );
      }
    }

    /// <summary>
    /// Deletes the zipcode.
    /// </summary>
    /// <param name="zipcode">The zipcode.</param>
    public void DeleteZipcode( CityZipcode zipcode ) {
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {
          using ( SQLiteCommand command = CreateDeleteCommand( connection, zipcode.ZipcodeId ) ) {
            command.Transaction = transaction;
            try {
              command.ExecuteNonQuery( );
              transaction.Commit( );
            } catch ( Exception ex ) {
              transaction.Rollback( );
              throw new DataException( ex.Message, ex ); ;
            } finally {
              if ( connection.State != ConnectionState.Closed ) {
                connection.Close( );
              }
            }
          }
        }
      }
    }

    /// <summary>
    /// Gets the countries.
    /// </summary>
    /// <returns></returns>
    public List<Country> GetCountries( ) {
      List<Country> list = new List<Country>( );
      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteCommand command = CreateSelectCountriesCommand( connection ) ) {
          try {
            using ( SQLiteDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection ) ) {
              while ( reader.Read( ) ) {
                Country country = new Country( );
                country.CountryId = reader.GetInt32( reader.GetOrdinal( "ID" ) );
                country.CountryCode = reader.GetValue( reader.GetOrdinal( "CountryCode" ) ).ToString( );
                country.CountryName = reader.GetValue( reader.GetOrdinal( "Country" ) ).ToString( );
                list.Add( country );
              }
            }
          } catch ( Exception ex ) {
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }
      return list;
    }

    #endregion

    #region - Private methods -

    private void InsertZipcode( CityZipcode zipcode ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {

          SQLiteCommand command = CreateInsertCommand( connection, zipcode );
          command.Transaction = transaction;

          try {
            int val = command.ExecuteNonQuery( );

            command = DataHelper.CreateSelectAfterInsertCommand( connection, "ZipCodes" );
            zipcode.ZipcodeId = Convert.ToInt32( command.ExecuteScalar( ) );

            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex ); ;
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }
        }
      }

    }

    private void UpdateZipcode( CityZipcode zipcode ) {

      using ( SQLiteConnection connection = DataHelper.CreateConnection( ) ) {
        using ( SQLiteTransaction transaction = connection.BeginTransaction( ) ) {

          SQLiteCommand command = CreateUpdateCommand( connection, zipcode );
          command.Transaction = transaction;
          try {
            command.ExecuteNonQuery( );
            transaction.Commit( );
          } catch ( Exception ex ) {
            transaction.Rollback( );
            throw new DataException( ex.Message, ex );
          } finally {
            if ( connection.State != ConnectionState.Closed ) {
              connection.Close( );
            }
          }

        }
      }
    }

    #region - SQL statements -

    private SQLiteCommand CreateSelectZipcodeCommand( SQLiteConnection connection, int zipcodeId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT ZipCodes.[ID] AS ZipcodeId,
                              [ZipCodes].[ZipCode] AS Zipcode,
                              ZipCodes.[City] AS City,
                              Countries.[CountryCode] AS CountryCode,
                              Countries.[Country] AS Country,
                              Countries.[ID] AS CountryId
                              FROM ZipCodes INNER JOIN Countries ON ZipCodes.[CountryId]=Countries.Id
                              WHERE ZipCodes.ID = @zipcodeid";
      command.Parameters.AddWithValue( "@zipcodeid", zipcodeId );

      return command;
    }

    private SQLiteCommand CreateSelectAllCommand( SQLiteConnection connection ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT ZipCodes.[ID] AS ZipcodeId,
                              [ZipCodes].[ZipCode] AS Zipcode,
                              ZipCodes.[City] AS City,
                              Countries.[CountryCode] AS CountryCode,
                              Countries.[Country] AS Country,
                              Countries.[ID] AS CountryId
                              FROM ZipCodes INNER JOIN Countries ON ZipCodes.[CountryId]=Countries.Id";

      return command;
    }

    private SQLiteCommand CreateSelectByCountryCommand( SQLiteConnection connection, int countryId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT ZipCodes.[ID] AS ZipcodeId,
                              [ZipCodes].[ZipCode] AS Zipcode,
                              ZipCodes.[City] AS City,
                              Countries.[CountryCode] AS CountryCode,
                              Countries.[Country] AS Country,
                              Countries.[ID] AS CountryId
                              FROM ZipCodes INNER JOIN Countries ON ZipCodes.[CountryId]=Countries.Id
                              WHERE ZipCodes.CountryId = @countryid";
      command.Parameters.AddWithValue( "@countryid", countryId );
      return command;
    }

    private SQLiteCommand CreateSelectCountriesCommand( SQLiteConnection connection ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"SELECT ID, CountryCode, Country
                              FROM Countries";

      return command;
    }

    private SQLiteCommand CreateInsertCommand( SQLiteConnection connection, CityZipcode zipcode ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"INSERT INTO ZipCodes (ZipCode, City, CountryId)
                              VALUES (@zipcode, @city, @countryid)";
      command.Parameters.AddWithValue( "@zipcode", zipcode.Zipcode );
      command.Parameters.AddWithValue( "@city", zipcode.City );
      command.Parameters.AddWithValue( "@countryid", zipcode.CountryId );

      return command;
    }

    private SQLiteCommand CreateUpdateCommand( SQLiteConnection connection, CityZipcode zipcode ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"UPDATE ZipCodes SET ZipCode = @zipcode, 
                              City = @city, CountryId = @countryid
                              WHERE ID = @id";
      command.Parameters.AddWithValue( "@zipcode", zipcode.Zipcode );
      command.Parameters.AddWithValue( "@city", zipcode.City );
      command.Parameters.AddWithValue( "@countryid", zipcode.CountryId );
      command.Parameters.AddWithValue( "@id", zipcode.ZipcodeId );

      return command;
    }

    private SQLiteCommand CreateDeleteCommand( SQLiteConnection connection, int zipcodeId ) {
      SQLiteCommand command = connection.CreateCommand( );
      command.CommandText = @"DELETE FROM ZipCodes WHERE ID = @id";
      command.Parameters.AddWithValue( "@id", zipcodeId );

      return command;
    }

    #endregion
    
    #endregion
    
  }
}
